package com.kailong.dao.admin;

import com.kailong.dao.BaseDao;
import com.kailong.pojo.Seat;
import com.kailong.pojo.Student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class ManageStudentDao {
    public static  int insert(Student student){
        String sql = "insert into student values (?,?,?,?,?,?,?,?)";
        Object [] params = {
                student.getSno(),
                student.getSname(),
                student.getSsex(),
                student.getSphoneNumber(),
                student.getSclass(),
                student.getSmajor(),
                student.getScollege(),
                student.getPassword()
        };
        return BaseDao.exectuIUD(sql,params);
    }
    public static ArrayList<Student> selectAll(int cpage,int count,String college,String sex){
        ArrayList<Student> list = new ArrayList<>();
        //声明结果集
        ResultSet rs = null;
        //获取连接对象
        Connection conn = BaseDao.getconn();

        PreparedStatement ps = null;
        try{
            String sql = "";
            if (college!=null && sex!=null){
                sql = "select * from student where scollege = ? and ssex = ? order by sno  limit ?,?";
                ps = conn.prepareStatement(sql);
                ps.setString(1,college);
                ps.setString(2,sex);
                ps.setInt(3,(cpage-1)*count);
                ps.setInt(4,count);
            }else{
                sql = "select * from student order by sno desc limit ?,?";
                ps = conn.prepareStatement(sql);

                ps.setInt(1,(cpage-1)*count);
                ps.setInt(2,count);
            }
            rs = ps.executeQuery();
            while (rs.next()){
                Student s = new Student(
                        rs.getString("sno"),
                        rs.getString("sname"),
                        rs.getString("ssex"),
                        rs.getString("sphoneNumber"),
                        rs.getString("sclass"),
                        rs.getString("smajor"),
                        rs.getString("scollege"),
                        rs.getString("spassword")
                );
                list.add(s);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            BaseDao.closeall(rs,ps,conn);
        }


        return  list;
    }
    /**
     * 获取总记录数和总页数
     * @param count
     * @return
     */
    public  static int[] totalPage(int count,String college,String sex){
        //0,总记录数1,总页数
        int arr[] = {0,1};
        Connection conn = BaseDao.getconn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "";
            if(college != null && sex!= null){
                sql = "select count(*) from student where scollege = ? and ssex = ? ";
                ps = conn.prepareStatement(sql);
                ps.setString(1,college);
                ps.setString(2,sex);

            }else {
                sql = "select count(*) from student";
                ps = conn.prepareStatement(sql);
            }

            rs = ps.executeQuery();
            while(rs.next()){
                arr[0] = rs.getInt(1);
                if(arr[0] / count == 0){
                    arr[1] = arr[0]/count;
                }else {
                    arr[1] = arr[0]/count + 1;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeall(rs,ps,conn);
        }
        return  arr;
    }
    public  static int updatePwd(String sno,String pwd){
        int count =0;
        Connection conn = BaseDao.getconn();
        PreparedStatement ps = null;
        try {
            String sql = "update student set spassword = ? where sno = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,pwd);
            ps.setString(2,sno);
            count = ps.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            BaseDao.closeall(null,ps,conn);
        }
        return count;
    }
}
